-- 2012-05-17 EM
alter procedure sp_get_deposits_SubSum
@OwnerAcc_id int,
@sum money output
as
begin
/*
	select  @sum=convert(varchar(10), sum(a.summary), 1) from dbdata.dbo.accounts as a
			join dbdata.dbo.account_categs as ac on categ_id = ac.id 
			join dbdata.dbo.users as u on u.id=a.uid
			where 
		 u.dt_locked is null and
			code='6' and a.enabled=1 and a.OwnerAcc_id=@OwnerAcc_id
*/			

declare @tbl table(O_id int, list varchar(2048), summ money);

insert into @tbl
	SELECT a1.OwnerAcc_id,
       ( SELECT cast(id as varchar) + ','

           FROM dbdata.dbo.accounts as  a2

          WHERE a2.OwnerAcc_id = a1.OwnerAcc_id 

          ORDER BY id

            FOR XML PATH('') ) AS List, 0

    FROM dbdata.dbo.accounts as a1
    			join dbdata.dbo.account_categs as ac on a1.categ_id = ac.id 
			join dbdata.dbo.users as u on u.id=a1.uid
			where 
		 u.dt_locked is null and code='6' and a1.enabled=1
    and a1.OwnerAcc_id is not null and a1.OwnerAcc_id >= @OwnerAcc_id
    GROUP BY OwnerAcc_id ;
      
update @tbl set list=','+list

--select * from @tbl

declare @O_id int, @list varchar(2048), @summ money;

DECLARE c CURSOR FOR
        SELECT O_Id, List
          FROM @tbl
         ORDER BY O_Id;

        OPEN c ;

        FETCH NEXT FROM c INTO @O_id, @list ;

        WHILE @@FETCH_STATUS = 0 
			BEGIN
				update @tbl set list=REPLACE(list,','+CAST(@o_id as varchar)+',',','+CAST(@o_id as varchar)+@list);
            


        FETCH NEXT FROM c INTO @O_id, @list;

        END;


        CLOSE c ;


        OPEN c ;

        FETCH NEXT FROM c INTO @O_id, @list ;

        WHILE @@FETCH_STATUS = 0 
			BEGIN
--print @list
				select @summ=sum(summary) 
					from dbdata.dbo.accounts as a
					  join dbdata.dbo.account_categs as ac on a.categ_id = ac.id 
						join dbdata.dbo.users as u on u.id=a.uid
				where 
				u.dt_locked is null and code='6' and a.enabled=1
					and  @list like  '%,'+cast(a.id as varchar)+',%' 
--print @summ
				update @tbl set summ=@summ where O_id=@O_id 
            


        FETCH NEXT FROM c INTO @O_id, @list;

        END;


        CLOSE c ;


DEALLOCATE c ;


--select * from @tbl

select @sum=summ from @tbl where O_id=@OwnerAcc_id;
			
end;
go

grant execute on sp_get_deposits_SubSum to Executer;

/*
DECLARE	@return_value int,
		@sum money

EXEC	@return_value = [dbo].[sp_get_deposits_SubSum]
		@OwnerAcc_id = 5,
		@sum = @sum OUTPUT

SELECT	@sum as N'@sum'

SELECT	'Return Value' = @return_value
*/
